0

So I ran a SQL query, got all the data I need, but now I need to match it. I think it is alittle bit more than standard VLOOKUP.

I'm performing a Reconciliation, and I am hoping there is something that exists.

I retrieved an ID code, Month of PAyment, and PAyment Amounts.

Within this SQL Query info, I need to verify with what I am comparing to, SO i need first match IDs, then Match the month of Payment. Once these two are true, I need it to return the amount. I can't do it with a simple VLOOKUP... Is there anything I can use?

heimy22
  • 13
  • 3
  • Just create an extra field that concatenates id and month of payment in both the datasets and then perform the lookup. It's much easier to have both the datasets in your SQL database so you could just use a join though – Radagast Oct 14 '21 at 21:11

2 Answers2

0

you can use an index match formula combining both columns together and use sum() on the result. this will ensure the lookup runs on the unique value. The formula should look like this =Index([Data Table Reference],match([ID]&[Payment],[Data Table Reference],0),column reference for the amount)) as a lookup formula won't work with multiple references

Amr Nasser
  • 16
  • 1
0

If you don't want to add an additional field with a concatenation of the two fields, you can use SUMIFS. Assuming the value you want to return is in column C and the sheet with the data is called DATA... in the sheet you want to return the value in, you would write something like....

SUMIFS(DATA!C:C, DATA!A:A, A1, DATA!B:B, B1)

Note, I have also assumed you would be looking to match values from column A in data sheet with column A in sheet you want to show values in. Same for column B. However you can adjust this formula to suit any setup. You can also easily add additional lookups.

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
NelliNoodle
  • 158
  • 1
  • 9