Below Formula working only for US and not for UK

[kishoremcp]

I have the below formula with me and works for US region but not for UK

Please Login or Register  to view this content.

Any help is appreciated.

Rates is a table naming (which actually resides in a different sheet.)

Any other formula to achieve similar output is also fine for me.

Thanks.

[JohnTopley]

What is VLOOKUP parameter: if a date then it could simple be a US vs UK date problem

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[kishoremcp]

i need the output in Resource Usage sheet in V13 Cell.

[Sam Capricci]

just offhand, maybe if / and to replace the if if...
=IF(AND(D1="US",D13="Offshore"),VLOOKUP(C13,Rates,3,0),IF(AND(D1="US",D13="Onsite"),VLOOKUP(C13,Rates,5,0),IF(AND(D1="UK",D13="Offshore"),VLOOKUP(C13,Rates,7,0),IF(AND(D1="UK",D13="Onsite"),VLOOKUP(C13,Rates,9,0)))))))))
(untested and might have to check the number of parens)
And also offhand, maybe UK has a space in D1 like "UK " which could cause your issue.

[JohnTopley]

Try

=IF(D1="US",IF(D13="Offshore",VLOOKUP(C13,Rates,3,0),IF(D13="Onsite",VLOOKUP(C13,Rates,5,0))),IF(D1="UK",IF(D13="Offshore",VLOOKUP(C13,Rates,7,0),IF(D13="Onsite",VLOOKUP(C13,Rates,9,0)))))

Or Shorter (assuming only Offshore or Onsite as options)

=IF(D1="US",IF(D13="Offshore",VLOOKUP(C13,Rates,3,0),VLOOKUP(C13,Rates,5,0)),IF(D1="UK",IF(D13="Offshore",VLOOKUP(C13,Rates,7,0),VLOOKUP(C13,Rates,9,0))))

[Sam Capricci]

actually this looks like it fixes it...
=IF(AND(D1="US",D13="Offshore"),VLOOKUP(C13,Rates,3,0),IF(AND(D1="US",D13="Onsite"),VLOOKUP(C13,Rates,5,0),IF(AND(D1="UK",D13="Offshore"),VLOOKUP(C13,Rates,7,0),IF(AND(D1="UK",D13="Onsite"),VLOOKUP(C13,Rates,9,0)))))

[xlnitwit]

Perhaps this
=INDEX(Rates,MATCH(C13,INDEX(Rates,,1),0),MATCH($D$1&" "&D13,Rates!$C$3:$N$3,0))

[Phuocam]

One way:

=VLOOKUP(C13,Rates,MATCH($D$1&" "&D13,Rates!$C$3:$N$3,0),0)